using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Caching.Memory;
using Microsoft.Extensions.Logging;
using Volo.Abp.DependencyInjection;
using Volo.Abp.Domain.Repositories;
using Volo.Abp.Domain.Services;
using CKY.AgentPlatform.Domain.EntityFrameworkCore;

namespace CKY.AgentPlatform.Domain.Performance
{
    /// <summary>
    /// 数据库性能优化服务
    /// 提供数据库查询优化、索引建议、缓存策略等功能
    /// </summary>
    public class DatabasePerformanceService : DomainService, ITransientDependency
    {
        private readonly IMemoryCache _cache;
        private readonly ILogger<DatabasePerformanceService> _logger;
        private readonly IServiceProvider _serviceProvider;

        public DatabasePerformanceService(
            IMemoryCache cache,
            ILogger<DatabasePerformanceService> logger,
            IServiceProvider serviceProvider)
        {
            _cache = cache;
            _logger = logger;
            _serviceProvider = serviceProvider;
        }

        /// <summary>
        /// 获取数据库连接
        /// </summary>
        private System.Data.Common.DbConnection GetDbConnection()
        {
            var dbContext = _serviceProvider.GetService(typeof(IAgentPlatformDbContext)) as IAgentPlatformDbContext;
            if (dbContext == null) return null;

            var database = dbContext.GetDatabase();
            return database.GetType().GetProperty("DbConnection")?.GetValue(database) as System.Data.Common.DbConnection;
        }

        /// <summary>
        /// 获取数据库性能统计信息
        /// </summary>
        public async Task<DatabasePerformanceStats> GetPerformanceStatsAsync()
        {
            var cacheKey = "database_performance_stats";

            return await _cache.GetOrCreateAsync(cacheKey, async entry =>
            {
                entry.AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(5);

                var stats = new DatabasePerformanceStats
                {
                    Timestamp = DateTime.UtcNow,
                    ConnectionCount = await GetConnectionCountAsync(),
                    ActiveTransactions = await GetActiveTransactionsCountAsync(),
                    CacheHitRate = await GetCacheHitRateAsync(),
                    AverageQueryTime = await GetAverageQueryTimeAsync(),
                    SlowQueryCount = await GetSlowQueryCountAsync(),
                    IndexUsageStats = await GetIndexUsageStatsAsync(),
                    TableSizeStats = await GetTableSizeStatsAsync()
                };

                return stats;
            });
        }

        /// <summary>
        /// 获取数据库连接数
        /// </summary>
        private async Task<int> GetConnectionCountAsync()
        {
            try
            {
                // 这里需要根据具体数据库类型实现不同的查询
                var connection = GetDbConnection();
                if (connection == null) return 0;
                await connection.OpenAsync();

                try
                {
                    var command = connection.CreateCommand();
                    command.CommandText = GetConnectionCountQuery();
                    var result = await command.ExecuteScalarAsync();
                    return Convert.ToInt32(result);
                }
                finally
                {
                    await connection.CloseAsync();
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取数据库连接数失败");
                return 0;
            }
        }

        /// <summary>
        /// 获取活跃事务数
        /// </summary>
        private async Task<int> GetActiveTransactionsCountAsync()
        {
            try
            {
                var connection = GetDbConnection();
                if (connection == null) return 0;
                await connection.OpenAsync();

                try
                {
                    var command = connection.CreateCommand();
                    command.CommandText = GetActiveTransactionsQuery();
                    var result = await command.ExecuteScalarAsync();
                    return Convert.ToInt32(result);
                }
                finally
                {
                    await connection.CloseAsync();
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取活跃事务数失败");
                return 0;
            }
        }

        /// <summary>
        /// 获取缓存命中率
        /// </summary>
        private async Task<double> GetCacheHitRateAsync()
        {
            try
            {
                var connection = GetDbConnection();
                if (connection == null) return 0;
                await connection.OpenAsync();

                try
                {
                    var command = connection.CreateCommand();
                    command.CommandText = GetCacheHitRateQuery();
                    var result = await command.ExecuteScalarAsync();
                    return Convert.ToDouble(result);
                }
                finally
                {
                    await connection.CloseAsync();
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取缓存命中率失败");
                return 0;
            }
        }

        /// <summary>
        /// 获取平均查询时间
        /// </summary>
        private async Task<double> GetAverageQueryTimeAsync()
        {
            try
            {
                var connection = GetDbConnection();
                if (connection == null) return 0;
                await connection.OpenAsync();

                try
                {
                    var command = connection.CreateCommand();
                    command.CommandText = GetAverageQueryTimeQuery();
                    var result = await command.ExecuteScalarAsync();
                    return Convert.ToDouble(result);
                }
                finally
                {
                    await connection.CloseAsync();
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取平均查询时间失败");
                return 0;
            }
        }

        /// <summary>
        /// 获取慢查询数量
        /// </summary>
        private async Task<int> GetSlowQueryCountAsync()
        {
            try
            {
                var connection = GetDbConnection();
                if (connection == null) return 0;
                await connection.OpenAsync();

                try
                {
                    var command = connection.CreateCommand();
                    command.CommandText = GetSlowQueryCountQuery();
                    var result = await command.ExecuteScalarAsync();
                    return Convert.ToInt32(result);
                }
                finally
                {
                    await connection.CloseAsync();
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取慢查询数量失败");
                return 0;
            }
        }

        /// <summary>
        /// 获取索引使用统计
        /// </summary>
        private async Task<List<IndexUsageStat>> GetIndexUsageStatsAsync()
        {
            try
            {
                var dbContext = _serviceProvider.GetService(typeof(IAgentPlatformDbContext)) as IAgentPlatformDbContext;
                if (dbContext == null) return new List<IndexUsageStat>();

                var database = dbContext.GetDatabase();
                var connection = database.GetType().GetMethod("GetDbConnection")?.Invoke(database, null) as System.Data.Common.DbConnection;
                await connection.OpenAsync();

                try
                {
                    var command = connection.CreateCommand();
                    command.CommandText = GetIndexUsageQuery();
                    var reader = await command.ExecuteReaderAsync();

                    var stats = new List<IndexUsageStat>();
                    while (await reader.ReadAsync())
                    {
                        stats.Add(new IndexUsageStat
                        {
                            TableName = reader.GetString(0),
                            IndexName = reader.GetString(1),
                            UsageCount = reader.GetInt32(2),
                            TotalSeeks = reader.GetInt32(3),
                            TotalScans = reader.GetInt32(4),
                            TotalLookups = reader.GetInt32(5),
                            TotalUpdates = reader.GetInt32(6)
                        });
                    }

                    return stats;
                }
                finally
                {
                    await connection.CloseAsync();
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取索引使用统计失败");
                return new List<IndexUsageStat>();
            }
        }

        /// <summary>
        /// 获取表大小统计
        /// </summary>
        private async Task<List<TableSizeStat>> GetTableSizeStatsAsync()
        {
            try
            {
                var dbContext = _serviceProvider.GetService(typeof(IAgentPlatformDbContext)) as IAgentPlatformDbContext;
                if (dbContext == null) return new List<TableSizeStat>();

                var database = dbContext.GetDatabase();
                var connection = database.GetType().GetMethod("GetDbConnection")?.Invoke(database, null) as System.Data.Common.DbConnection;
                if (connection == null) return new List<TableSizeStat>();
                await connection.OpenAsync();

                try
                {
                    var command = connection.CreateCommand();
                    command.CommandText = GetTableSizeQuery();
                    var reader = await command.ExecuteReaderAsync();

                    var stats = new List<TableSizeStat>();
                    while (await reader.ReadAsync())
                    {
                        stats.Add(new TableSizeStat
                        {
                            TableName = reader.GetString(0),
                            RowCount = reader.GetInt64(1),
                            TotalSpaceKB = reader.GetInt64(2),
                            DataSpaceKB = reader.GetInt64(3),
                            IndexSpaceKB = reader.GetInt64(4)
                        });
                    }

                    return stats;
                }
                finally
                {
                    await connection.CloseAsync();
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取表大小统计失败");
                return new List<TableSizeStat>();
            }
        }

        /// <summary>
        /// 获取数据库优化建议
        /// </summary>
        public async Task<List<DatabaseOptimizationRecommendation>> GetOptimizationRecommendationsAsync()
        {
            var stats = await GetPerformanceStatsAsync();
            var recommendations = new List<DatabaseOptimizationRecommendation>();

            // 检查慢查询
            if (stats.SlowQueryCount > 10)
            {
                recommendations.Add(new DatabaseOptimizationRecommendation
                {
                    Type = "SlowQuery",
                    Severity = "High",
                    Title = "慢查询过多",
                    Description = $"发现 {stats.SlowQueryCount} 个慢查询，建议优化查询或添加索引",
                    Recommendation = "检查执行计划，优化查询语句，考虑添加适当的索引"
                });
            }

            // 检查缓存命中率
            if (stats.CacheHitRate < 80)
            {
                recommendations.Add(new DatabaseOptimizationRecommendation
                {
                    Type = "CacheHitRate",
                    Severity = "Medium",
                    Title = "缓存命中率低",
                    Description = $"缓存命中率为 {stats.CacheHitRate}%，建议调整缓存策略",
                    Recommendation = "增加查询缓存，优化缓存失效策略"
                });
            }

            // 检查平均查询时间
            if (stats.AverageQueryTime > 500)
            {
                recommendations.Add(new DatabaseOptimizationRecommendation
                {
                    Type = "QueryPerformance",
                    Severity = "High",
                    Title = "查询响应时间过长",
                    Description = $"平均查询时间为 {stats.AverageQueryTime}ms，建议优化查询性能",
                    Recommendation = "优化查询语句，添加必要的索引，考虑数据库分区"
                });
            }

            // 检查索引使用情况
            var unusedIndexes = stats.IndexUsageStats.Where(i => i.UsageCount == 0).ToList();
            if (unusedIndexes.Count > 0)
            {
                recommendations.Add(new DatabaseOptimizationRecommendation
                {
                    Type = "UnusedIndex",
                    Severity = "Low",
                    Title = "发现未使用的索引",
                    Description = $"发现 {unusedIndexes.Count} 个未使用的索引，占用存储空间",
                    Recommendation = "删除不必要的索引以减少写入开销"
                });
            }

            return recommendations;
        }

        /// <summary>
        /// 清理数据库缓存
        /// </summary>
        public async Task ClearDatabaseCacheAsync()
        {
            // 由于IMemoryCache没有直接的Keys属性，我们需要使用其他方法来清理缓存
            // 这里创建一个缓存键的追踪列表，或者使用特定的缓存前缀来管理
            var cacheKeysToClear = new List<string>
            {
                "database_performance_stats",
                "database_connection_count",
                "database_cache_hit_rate",
                "database_table_sizes"
            };

            foreach (var key in cacheKeysToClear)
            {
                _cache.Remove(key);
            }

            _logger.LogInformation($"已清理 {cacheKeysToClear.Count} 个数据库缓存项");
        }

        #region Database Specific Queries

        private string GetConnectionCountQuery()
        {
            // 根据数据库类型返回不同的查询
            return "SELECT COUNT(*) FROM pg_stat_activity WHERE state = 'active';"; // PostgreSQL
        }

        private string GetActiveTransactionsQuery()
        {
            return "SELECT COUNT(*) FROM pg_stat_activity WHERE state = 'idle in transaction';"; // PostgreSQL
        }

        private string GetCacheHitRateQuery()
        {
            return "SELECT CAST(heap_blks_hit AS FLOAT) / (heap_blks_hit + heap_blks_read) * 100 FROM pg_statio_user_tables LIMIT 1;"; // PostgreSQL
        }

        private string GetAverageQueryTimeQuery()
        {
            return "SELECT AVG(total_time) FROM pg_stat_statements LIMIT 1;"; // PostgreSQL
        }

        private string GetSlowQueryCountQuery()
        {
            return "SELECT COUNT(*) FROM pg_stat_statements WHERE total_time > 1000;"; // PostgreSQL
        }

        private string GetIndexUsageQuery()
        {
            return @"
                SELECT
                    schemaname || '.' || tablename as TableName,
                    indexname as IndexName,
                    idx_scan as UsageCount,
                    idx_tup_read as TotalSeeks,
                    idx_tup_fetch as TotalScans,
                    n_tup_ins + n_tup_upd + n_tup_del as TotalLookups,
                    n_tup_upd as TotalUpdates
                FROM pg_stat_user_indexes
                ORDER BY idx_scan DESC;
            "; // PostgreSQL
        }

        private string GetTableSizeQuery()
        {
            return @"
                SELECT
                    schemaname || '.' || tablename as TableName,
                    n_tup_ins - n_tup_del as RowCount,
                    pg_total_relation_size(schemaname||'.'||tablename) as TotalSpaceKB,
                    pg_relation_size(schemaname||'.'||tablename) as DataSpaceKB,
                    pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename) as IndexSpaceKB
                FROM pg_stat_user_tables
                ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
            "; // PostgreSQL
        }

        #endregion
    }

    /// <summary>
    /// 数据库性能统计信息
    /// </summary>
    public class DatabasePerformanceStats
    {
        public DateTime Timestamp { get; set; }
        public int ConnectionCount { get; set; }
        public int ActiveTransactions { get; set; }
        public double CacheHitRate { get; set; }
        public double AverageQueryTime { get; set; }
        public int SlowQueryCount { get; set; }
        public List<IndexUsageStat> IndexUsageStats { get; set; }
        public List<TableSizeStat> TableSizeStats { get; set; }
    }

    /// <summary>
    /// 索引使用统计
    /// </summary>
    public class IndexUsageStat
    {
        public string TableName { get; set; }
        public string IndexName { get; set; }
        public int UsageCount { get; set; }
        public int TotalSeeks { get; set; }
        public int TotalScans { get; set; }
        public int TotalLookups { get; set; }
        public int TotalUpdates { get; set; }
    }

    /// <summary>
    /// 表大小统计
    /// </summary>
    public class TableSizeStat
    {
        public string TableName { get; set; }
        public long RowCount { get; set; }
        public long TotalSpaceKB { get; set; }
        public long DataSpaceKB { get; set; }
        public long IndexSpaceKB { get; set; }
    }

    /// <summary>
    /// 数据库优化建议
    /// </summary>
    public class DatabaseOptimizationRecommendation
    {
        public string Type { get; set; }
        public string Severity { get; set; }
        public string Title { get; set; }
        public string Description { get; set; }
        public string Recommendation { get; set; }
    }
}